import json
from biz.IFile import IFile
convertCH={
        'xqm':'校区码',
        'yxm':'院系码',
        'zym':'专业码',
        'xqmc':'校区名称',
        'dwmc':'院系名称',
        'zymc':'专业名称',
        'pycc':'培养层次'
    }
class sql(IFile):

    def __init__(self):
        self.SERVICE_NAME = ''  # 业务名称
        self.groupby = ''
        self.aggregate = 'sum'
        self.where = ''
        self.metric = '' # 用于构建学生主表，与groupby字段区别在于字段码

    def write(self,conn):
        sql: str =self.get()
        cursor=conn.cursor()
        cursor.execute(sql)
        # 获取查询结果
        results = cursor.fetchall()
        result_list = []
        for row in results:
            result_dict = {}
            # 根据结果的列名获取对应的值，并将其添加到字典中
            for idx, col_name in enumerate(cursor.description):
                result_dict[col_name[0]] = row[idx]
            result_list.append(result_dict)

        # 将结果转换为 JSON 字符串
        json_str = json.dumps(result_list, ensure_ascii=False)
        with open("json_data", "a") as f:
            f.write(json_str)
            f.write("\n")
        print(json_str)

    def convert_CH(self, groupby):
        '''
        转变中文
        :param groupby:
        :return:
        '''
        data = groupby.split(',')
        for i in range(len(data)):
            data[i] = data[i] + ' ' + convertCH[data[i].strip()]  # 删去空格，防止因空格不匹配
        return ','.join(data)

    def get_Primary(self, metric: str):
        '''
        构建学生表
        :param metric: 查询指标
        :return:
        '''
        sql = ['select xs.* ', ',zy.zymc', ',dw.dwmc', ',xq.xqmc'
            , 'from T_XSJBXX xs ', 'LEFT JOIN T_ZY zy on zy.zydm=xs.zym and zy.yxdm=xs.yxm '
            , 'left join T_DW dw on dw.dwm=xs.yxm ', 'left join T_XQ xq on xq.xqdm=xs.xqm ']
        dicts = {"zymc": [1, 5], "dwmc": [2, 6], "xqmc": [3, 7]}
        filters = list(set(['zymc', 'dwmc', 'xqmc']) - set(metric.split(',')))
        if len(filters) == 0:
            return ' '.join(sql)
        indexes_to_remove = []
        for f in filters:
            indexes_to_remove += dicts.get(f)
            # 使用列表推导式生成新数组
        new_array = [element for index, element in enumerate(sql) if index not in indexes_to_remove]
        return ' '.join(new_array)

    def get(self):
        '''
        返回最终的执行SQL
        :param groupby:
        :return:
        '''
        aggre = ''
        if self.aggregate == 'sum':
            # 这里需要转成int型，不然json转换会报错
            aggre = 'cast(sum(num) as UNSIGNED) "{}学生数"'.format(self.SERVICE_NAME)
        if self.aggregate == 'avg':
            aggre = 'round(100*avg(num),2) "{}学生数"'.format(self.SERVICE_NAME)
        print(self.convert_CH(self.groupby))
        sql = '''
        select {} ,{} from ({})new group by {}
        '''.format(self.convert_CH(self.groupby), aggre, self.get_sql(self.groupby, self.where), self.groupby)
        print(">>>>>{}.{}>>>{}".format(type(self).__name__, 'get', sql))
        return sql

    def get_sql(self, groupby: str, where: str) -> str:
        '''
        最后的指标
        :param where:
        :param groupby: 校区名称，院系名称，专业名称，培训层次
        :return:
        '''

        sql = '''
        select {},case when t.xh is null then 0 else 1 end num from ({})t

        right join ({})xs on t.xh=xs.xh
        '''.format(groupby, self.unique_sql(self.where), self.get_Primary(self.metric))
        print(">>>>>{}.{}>>>{}".format(type(self).__name__, 'get_sql', sql))
        return sql

    def unique_sql(self, where) -> None:
        '''
        业务sql
        :param where:
        :return:
        '''
        pass
